from datetime import datetime,timedelta
from sqlalchemy import create_engine
import os
try:
    from hashlib import sha1
except ImportError:
    sys.exit('ImportError: No module named hashlib\n'
             'If you are on python2.4 this library is not part of python. '
             'Please install it. Example: easy_install hashlib')
#engine = create_engine('sqlite:///:memory:', echo=True)
hierher = os.path.join(os.getcwd(),"db.db")
engine = create_engine('sqlite:///%s'%hierher, echo=True)
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey, Unicode, Date, DateTime,Boolean
metadata = MetaData()
from sqlalchemy.orm import sessionmaker,mapper,relation,backref,synonym
from sqlalchemy.sql import select
from sqlalchemy.orm.interfaces import MapperExtension
Session = sessionmaker(bind=engine)
Session.configure(bind=engine)
session = Session()
import genshi # for some handy decorators
from urllib import urlencode # generate links (bad idea here)

def utcnow():
    return datetime.utcnow()

class User(object):
    def __init__(self,user_name=u'',display_name=u'',email_address=u''):
        """
        """
        self.utcnow         =   utcnow()
        self.display_name   =   display_name
        self.user_name      =   user_name
        self.email_address  =   email_address

    def __repr__(self):
        return '<User: display_name=%s, user_name=%s, email_address=%s>'%\
        (self.display_name,self.user_name,self.email_address)

    def _set_password(self, password):
        """Hash ``password`` on the fly and store its hashed version."""
        hashed_password = password

        if isinstance(password, unicode):
            password_8bit = password.encode('UTF-8')
        else:
            password_8bit = password

        salt = sha1()
        salt.update(os.urandom(60))
        hash = sha1()
        hash.update(password_8bit + salt.hexdigest())
        hashed_password = salt.hexdigest() + hash.hexdigest()

        # Make sure the hashed password is an UTF-8 object at the end of the
        # process because SQLAlchemy _wants_ a unicode object for Unicode
        # columns
        if not isinstance(hashed_password, unicode):
            hashed_password = hashed_password.decode('UTF-8')

        self._password = hashed_password

    def _get_password(self):
        """Return the hashed version of the password."""
        return self._password

    password = synonym('_password', descriptor=property(_get_password,
                                                        _set_password))

    def validate_password(self, password):
        """
        Check the password against existing credentials.

        :param password: the password that was provided by the user to
            try and authenticate. This is the clear text version that we will
            need to match against the hashed one in the database.
        :type password: unicode object.
        :return: Whether the password is valid.
        :rtype: bool

        """

        # FIXME!!!!!!!!!! Find out why checking the hashed values doesnt work
        password = password.encode('utf-8')
        hashed_pass = sha1()
        hashed_pass.update(password + self.password[:40])
        return self.password == password
        return self.password[40:] == hashed_pass.hexdigest()

    # define some shortcuts to get to the contracts

    def owned_records(self):
        """ the records in the group where group_name == self.user_name """
        # SLOW AND INEFFICIENT - THIS SHOULD BE DONE BY THE DBASE, NOT IN PYTHON
        s = session.query(Record).all()
        r = []
        for x in s:
            readable = x.read_groups
            writable = x.write_groups
            for g in readable:
                if g.group_name == self.user_name:
                    r.append(x)
            for g in writable:
                if g.group_name == self.user_name:
                    r.append(x)
        return r


class Permission(object):
    def __init__(self,permission_name=u''):
        self.permission_name    =   permission_name
        self.description        =   u""

    def __repr__(self):
        return '<Permission: permission_name=%s>'%(self.permission_name)


class Group(object):
    def __init__(self,group_name=u'',display_name=u''):
        self.utcnow         =   utcnow()
        self.group_name     =   group_name

    def __repr__(self):
        return '<Group: group_name=%s>'%\
        (self.group_name)


class Record(object):
    def __init__(self):
        self.utcnow         =   utcnow()

    def __repr__(self):
        return '<Contract: title=%s, description=%s>'%\
        (self.title,self.description)

    @property
    def id_link(self):
        return genshi.XML('<a href="/record/view?id=%d">%d</a>'%(self.record_id,self.record_id))

    @property
    def title_link(self):
        return genshi.XML('<a href="/record/view?id=%d">%s</a>'%(self.record_id,self.title))

    @property
    def coversheet_link(self):
        return genshi.XML('<a href="/record/coversheet/%d">Coversheet for %s</a>'%(self.record_id,self.title))


class Party(object):
    def __init__(self):
        self.utcnow         =   utcnow()

    def __repr__(self):
        return '<Party: name=%s>'%self.name

    def saneName(self):
        x = urlencode(dict(a=self.name)).split("=")[1]
        return x

    def safeName(self):
        return self.name.replace("&", "&amp;")

    @property
    def id_link(self):
        """ Genshi chokes on special chars, so replace them with the html encoded equivalents"""
        return genshi.XML('<a href="/party/view/%d">%s</a>'%(self.party_id,self.safeName()))

    @property
    def avgRisk(self):
        """
        This doesn't tell us much...
        """
        total_votes_for_party = len(self.risks)
        if total_votes_for_party == 0: # otherwise a divide by zero below
            return 0
        total_accumulated_for_party = sum([x.risk for x in self.risks])
        total_avg_for_party = float(total_accumulated_for_party/total_votes_for_party)
        return int(total_avg_for_party) # round down? else import floor or ceiling

    @property
    def risk2txt(self):
        """ Return a user readable description of the risk """
        risks =  {  0:'No Risk Data',\
                    1:'Very Low Risk',\
                    2:'Low Risk',\
                    3:'Medium Risk',\
                    4:'High Risk',\
                    5:'Very High Risk',\
                }
        try:
            risk_text = risks[self.avgRisk]
        except Exception,e:
            return str(e)
        else:
            return risk_text

class Doc(object):
    def __init__(self):
        self.utcnow         =   utcnow()
        self.md5            =   ""
        self.name           =   u""
        self.orig_name      =   u""
        self.size           =   0
        self.fulltext       =   "" # can we somehow extract text for searching?

    def __repr__(self):
        return '<Document: path=%s>'%\
        (self.name)

    @property
    def id_link(self):
        return genshi.XML('<a href="/document/serve/%d">%s</a>'%(self.doc_id,self.orig_name))

class Reminder(object):
    def __init__(self):
        self.utcnow = utcnow()

    def __repr__(self):
        return '<Reminder trigger:%s, message:%s, event_id:%s>'%(self.trigger, self.message,self.event_id)

    def getTriggerDate(self):
        eventStartDate = self.event.starts
        reminderDelta = timedelta(days=self.trigger)
        retDate = eventStartDate-reminderDelta
        return retDate

    @property
    def triggerDate(self):
        return self.getTriggerDate()

class Event(object):
    def __init__(self):
        self.utcnow         =   utcnow()

    def __repr__(self):
        return '<Event: title=%s, desc:%s, occurs=%s>'%\
        (self.title,self.description,self.starts)

    @property
    def fmtOccurs(self):
        return datetime.strftime(self.starts,"%Y-%m-%d")

    @property
    def fmtEnds(self):
        return datetime.strftime(self.ends,"%Y-%m-%d")

class EventType(object):
    def __repr__(self):
        return '<EventType: type=%s, description=%s>'%(self.type,self.description)

class Risk(object):
    def __init__(self,risk):
        self.utcnow         =   utcnow()
        self.risk           =   risk

    def __repr__(self): #probably never needed
        return '<Risk: risk:%d>'%self.risk

class Signatory(object):
    def __init__(self):
        self.utcnow         =   utcnow()
        self.name           =   u""
        self.title          =   u""
        self.signed_on      =   None

    def __repr__(self):
        return '<Signatory: name=%s, title=%s, signed_on=%s>'%(self.name,self.title,self.signed_on)

class Tag(object):
    def __init__(self):
        self.title          =   u""

    def __repr__(self):
        return '<Tag: title=%s>'%self.title

class Term(object):
    """ Many to one relationship to Record """
    def __init__(self):
        self.utcnow = utcnow()

    def __repr__(self):
        return '<Term type:%s description:%s, achieving:%s>'%(self.type,self.description,self.achieving)

# NUE stands for NonUserEmail
class NUE(object):
    """ Holds emails which are not associated with lifepyscle users. Typically for reminders """
    def __init__(self,email):
        self.utcnow = utcnow()
        self.email = email

    def __repr__(self):
        return '<NonUserEmail email:%s>'%self.email

user_group_table = Table('user_group', metadata,
    Column('user_id', Integer, ForeignKey('user.user_id',onupdate="CASCADE")),
    Column('group_id', Integer, ForeignKey('group.group_id',onupdate="CASCADE"))
)

group_permission_table = Table('group_permission', metadata,
    Column('group_id', Integer, ForeignKey('group.group_id',
        onupdate="CASCADE", ondelete="CASCADE")),
    Column('permission_id', Integer, ForeignKey('permission.permission_id',
        onupdate="CASCADE", ondelete="CASCADE"))
)

user_reminder_table = Table('user_reminder', metadata,
    Column('user_id', Integer, ForeignKey('user.user_id')),
    Column('reminder_id', Integer, ForeignKey('reminder.reminder_id'))
)

nue_reminder_table = Table('nue_reminder',metadata,
    Column('nue_id', Integer, ForeignKey('nue.nue_id')),
    Column('reminder_id',Integer,ForeignKey('reminder.reminder_id'))
)

doc_signatory_table = Table('doc_signatory', metadata,
    Column('doc_id', Integer, ForeignKey('doc.doc_id',
        onupdate="CASCADE", ondelete="CASCADE")),
    Column('signatory_id', Integer, ForeignKey('signatory.signatory_id',
        onupdate="CASCADE", ondelete="CASCADE"))
)

record_tag_table = Table('record_tag', metadata,
    Column('record_id', Integer, ForeignKey('record.record_id',
        onupdate="CASCADE", ondelete="CASCADE")),
    Column('tag_id', Integer, ForeignKey('tag.tag_id',
        onupdate="CASCADE", ondelete="CASCADE"))
)

party_tag_table = Table('party_tag', metadata,
    Column('party_id', Integer, ForeignKey('party.party_id',
        onupdate="CASCADE", ondelete="CASCADE")),
    Column('tag_id', Integer, ForeignKey('tag.tag_id',
        onupdate="CASCADE", ondelete="CASCADE"))
)

event_tag_table = Table('event_tag', metadata,
    Column('event_id', Integer, ForeignKey('event.event_id',
        onupdate="CASCADE", ondelete="CASCADE")),
    Column('tag_id', Integer, ForeignKey('tag.tag_id',
        onupdate="CASCADE", ondelete="CASCADE"))
)

doc_tag_table = Table('doc_tag', metadata,
    Column('doc_id', Integer, ForeignKey('doc.doc_id',
        onupdate="CASCADE", ondelete="CASCADE")),
    Column('tag_id', Integer, ForeignKey('tag.tag_id',
        onupdate="CASCADE", ondelete="CASCADE"))
)

record_party_table = Table('record_party', metadata,
    Column('record_id',Integer, ForeignKey('record.record_id')),
    Column('party_id', Integer, ForeignKey('party.party_id'))
)

record_read_user_table = Table('record_read_user', metadata,
    Column('record_id',Integer, ForeignKey('record.record_id')),
    Column('user_id',Integer, ForeignKey('user.user_id')),
)

record_write_user_table = Table('record_write_user', metadata,
    Column('record_id',Integer, ForeignKey('record.record_id')),
    Column('user_id',Integer, ForeignKey('user.user_id')),
)

user_table = Table('user', metadata,
    Column('user_id', Integer, primary_key=True),
    Column('display_name', Unicode(50),index=True),
    Column('user_name',Unicode(25), unique=True,index=True),
    Column('email_address',Unicode(100), nullable=False,info={'rum': {'field':'Email'}}),
    Column('password', Unicode(80), info={'rum': {'field':'Password'}}),
    Column('last_updated', DateTime, onupdate=utcnow()),
)

permission_table = Table('permission',metadata,
    Column('permission_id', Integer, primary_key=True),
    Column('permission_name', Unicode(25),index=True,nullable=False),
    Column('description', Unicode(255),nullable=True)
)

group_table = Table('group', metadata,
    Column('group_id', Integer, primary_key=True),
    Column('parent_id',Integer,ForeignKey('group.group_id')),
    Column('group_name', Unicode(25), unique=True,index=True),
    Column('last_updated', DateTime, onupdate=utcnow()),
)

party_risk_table = Table('party_risk', metadata,
    Column('id', Integer, primary_key=True),
    Column('party_id', Integer, ForeignKey('party.party_id')),
    Column('risk',Integer(1))
)

record_table = Table('record', metadata,
    Column('record_id', Integer, primary_key=True),
    Column('ean13',String(13),nullable=False,index=True), # unique ean for each record at init
    Column('parent_id', Integer, ForeignKey('record.record_id')),
    Column('owner_id',Integer,ForeignKey('user.user_id'),nullable=False),
    Column('title', Unicode(99),index=True),#index!
    Column('description', Unicode(255),index=True),#index!
    Column('last_updated', DateTime, onupdate=utcnow()),
)

party_table = Table('party', metadata,
    Column('party_id', Integer, primary_key=True),
    Column('parent_id', Integer, ForeignKey('party.party_id')),
    Column('name', Unicode(99), unique=True,index=True),#index!
    Column('nick', Unicode(25), index=True),
    Column('last_updated', DateTime, onupdate=utcnow()),
)

event_table = Table('event', metadata,
    Column('event_id', Integer, primary_key=True),
    Column('title', Unicode(25),nullable=False,index=True),#index!
    Column('description', Unicode(255),index=True),#index!
    Column('starts',DateTime,nullable=False,index=True),
    Column('ends',DateTime,index=True,),
    Column('ends_str', Unicode(255)), # what user actually entered
    Column('last_updated', DateTime, onupdate=utcnow()),
    Column('event_type_id', Integer, ForeignKey('event_type.event_type_id')),
    Column('record_id',Integer,ForeignKey('record.record_id',ondelete="CASCADE")),
    Column('achieved', Boolean,default=False),
    # should we enter a field to hold ics information?
)

reminder_table = Table('reminder', metadata,
    Column('reminder_id', Integer, primary_key=True),
    Column('message', Unicode(255), index=True),
    Column('event_id', Integer, ForeignKey('event.event_id',ondelete="CASCADE")),
    Column('trigger', Integer),# store the trigger not an actual datetime as event could change
    Column('status',Integer,default=0)
)

nue_table = Table('nue',metadata,
    Column('nue_id', Integer,primary_key=True),
    Column('email', Unicode(255),index=True)
)

event_type_table = Table('event_type',metadata,
    Column('event_type_id',Integer,primary_key=True),
    Column('type', Unicode(50),nullable=False,index=True),
    Column('description',Unicode(255),nullable=True)
)

doc_table = Table('doc', metadata,
    Column('doc_id',Integer,primary_key=True),
    Column('md5',String(32),nullable=False,index=True),# easier to identify dupes
    Column('name',Unicode(999),nullable=False,index=True), # index!
    Column('orig_name',Unicode(99),nullable=False,index=True),
    Column('size',Integer,nullable=True),
    Column('fulltext',Unicode(),nullable=True,index=True), # index!
    Column('last_updated', DateTime, onupdate=utcnow()),
    Column('record_id',ForeignKey('record.record_id')),# CASCADE on delete would leave orphaned docs in the uploads directory
)

tag_table = Table('tag',metadata,
    Column('tag_id',Integer,primary_key=True),
    Column('title',Unicode(99),nullable=False,index=True)
)

term_table = Table('term',metadata,
    Column('term_id', Integer, primary_key=True),
    Column('type',Unicode(1),nullable=False,index=True,default='o'),# r for right, o for obligation
    Column('description', Unicode(), nullable=False, index=True),
    Column('achieving', Boolean,default=False),
    Column('record_id', ForeignKey('record.record_id', ondelete="CASCADE"))
)

signatory_table = Table('signatory',metadata,
    Column('signatory_id',Integer,primary_key=True),
    Column('name', Unicode(99), nullable=False,index=True),
    Column('title',Unicode(99),nullable=False,index=True),
    Column('signed_on', DateTime),
    Column('last_updated', DateTime,onupdate=utcnow()),
)

mapper(Record, record_table, properties={
    'parties': relation(Party,secondary=record_party_table, backref='records',lazy=True),
    'users_read': relation(User,secondary=record_read_user_table, backref='readable_records',lazy=True),
    'users_write': relation(User,secondary=record_write_user_table, backref='writable_records',lazy=True),
    'events': relation(Event,backref='record',cascade="all, delete, delete-orphan",passive_deletes=True),
    'subs': relation(Record, backref=backref('parent',remote_side=[record_table.c.record_id]), lazy=False, join_depth=2),
    'tags': relation(Tag,secondary=record_tag_table, backref='records',lazy=True),
    'docs': relation(Doc,backref='record'),
    'terms': relation(Term, backref='record',cascade='all, delete, delete-orphan',passive_deletes=True),
})

mapper(Term,term_table)

mapper(Tag,tag_table, properties={
    'events': relation(Event,secondary=event_tag_table, backref='tags',lazy=True)})

mapper(Signatory, signatory_table)

mapper(Event, event_table, properties={
    'reminders': relation(Reminder,backref='event',cascade="all,delete-orphan",passive_deletes=True)
})

mapper(Permission, permission_table)

mapper(Reminder, reminder_table,properties={
    'non_user_emails':relation(NUE,secondary=nue_reminder_table,backref='reminders',lazy=True)
})

mapper(NUE, nue_table)

mapper(Doc, doc_table, properties={
    'tags': relation(Tag,secondary=doc_tag_table,backref='docs',lazy=True),
    'sigs': relation(Signatory,secondary=doc_signatory_table,backref='docs',lazy=True),
})

mapper(EventType,event_type_table,properties={
    'events': relation(Event,backref='type')
})

mapper(User, user_table,properties={
    'records': relation(Record,backref='owner',cascade="all, delete, delete-orphan",passive_deletes=True),
    'reminders': relation(Reminder,secondary=user_reminder_table, backref='users',lazy=True),
})

mapper(Party, party_table, properties={
    'subs': relation(Party, backref=backref('parent', remote_side=[party_table.c.party_id]), lazy=False, join_depth=2),
    'tags': relation(Tag,secondary=party_tag_table,backref='parties',lazy=True),
    'risks': relation(Risk,backref='party')
})

mapper(Risk,party_risk_table)

mapper(Group, group_table, properties={
    'users': relation(User, secondary=user_group_table, backref='groups'),
    'subs': relation(Group, backref=backref('parent', remote_side=[group_table.c.group_id]), lazy=False, join_depth=2),
    'permissions': relation(Permission, secondary=group_permission_table, backref='groups'),
})

def createAll():
    metadata.create_all(engine)

def renew():
    if os .path.isfile(hierher):
        os.unlink(hierher)
    createAll()
